PIP Installation

In [1]:
pip install plotly==5.18.0
pip install dash==0.29.0
pip install dash plotly
pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
pip install gspread
pip install gspread google-auth
  Cell In[1], line 1
    pip install plotly==5.18.0
        ^
SyntaxError: invalid syntax

Google Authentication

In [7]:
import os.path
import pandas as pd
import plotly.express as px

from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError

# If modifying these scopes, delete the file token.json.
SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"]

def create_dataframe(spreadsheet_id, range_name):
    """Create a DataFrame from a Google Spreadsheet."""
    creds = None
    if os.path.exists("token.json"):
        creds = Credentials.from_authorized_user_file("token.json", SCOPES)
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                "credentials.json", SCOPES
            )
            creds = flow.run_local_server(port=0)
        with open("token.json", "w") as token:
            token.write(creds.to_json())

    try:
        service = build("sheets", "v4", credentials=creds)

        # Call the Sheets API
        sheet = service.spreadsheets()
        result = sheet.values().get(spreadsheetId=spreadsheet_id, range=range_name).execute()
        values = result.get("values", [])

        if not values:
            print("No data found.")
            return pd.DataFrame()  # Return an empty DataFrame if no data is found

        # Convert the values to a pandas DataFrame, skipping the first row
        df = pd.DataFrame(values[1:], columns=values[0])

        return df
    except HttpError as err:
        print(err)
        return pd.DataFrame()  # Return an empty DataFrame in case of an error

Net Sales By Region

In [8]:
# Access spreadsheet
SPREADSHEET_ID = "1e1EvgnSxAce8OcCqXCvBsnUQVlCHPIf5wA6e-UvA59c"
RANGE_NAME = "'Net Sales By Market'!A1:C61"

# Call the function to create a new DataFrame
market = create_dataframe(SPREADSHEET_ID, RANGE_NAME)
In [9]:
market = market.replace({',': '', '\.00': ''}, regex=True)
market["Sales"] = market["Sales"].astype(int)
#market.set_index("Year", inplace=True)
In [10]:
# Reverse year to get ascending order
r_market= market.iloc[::-1]
r_market
Out[10]:
Year Region Sales
59 2011 Rest of Asia Pacific 9902
58 2012 Rest of Asia Pacific 10741
57 2013 Rest of Asia Pacific 11181
56 2014 Rest of Asia Pacific 11248
55 2015 Rest of Asia Pacific 15093
54 2016 Rest of Asia Pacific 13654
53 2017 Rest of Asia Pacific 15199
52 2018 Rest of Asia Pacific 17407
51 2019 Rest of Asia Pacific 17788
50 2020 Rest of Asia Pacific 19593
49 2021 Rest of Asia Pacific 26356
48 2022 Rest of Asia Pacific 29375
47 2011 Japan 5437
46 2012 Japan 10571
45 2013 Japan 13462
44 2014 Japan 15314
43 2015 Japan 15706
42 2016 Japan 16928
41 2017 Japan 17733
40 2018 Japan 21733
39 2019 Japan 21506
38 2020 Japan 21418
37 2021 Japan 28482
36 2022 Japan 25977
35 2011 Greater China 12690
34 2012 Greater China 22533
33 2013 Greater China 25417
32 2014 Greater China 31853
31 2015 Greater China 58715
30 2016 Greater China 48492
29 2017 Greater China 44764
28 2018 Greater China 51942
27 2019 Greater China 43678
26 2020 Greater China 40308
25 2021 Greater China 68366
24 2022 Greater China 74200
23 2011 Europe 27778
22 2012 Europe 36323
21 2013 Europe 37883
20 2014 Europe 44285
19 2015 Europe 50337
18 2016 Europe 49952
17 2017 Europe 54938
16 2018 Europe 62420
15 2019 Europe 60288
14 2020 Europe 68640
13 2021 Europe 89307
12 2022 Europe 95118
11 2011 Americas 38315
10 2012 Americas 57512
9 2013 Americas 62739
8 2014 Americas 80095
7 2015 Americas 93864
6 2016 Americas 86613
5 2017 Americas 96600
4 2018 Americas 112093
3 2019 Americas 116914
2 2020 Americas 124556
1 2021 Americas 153306
0 2022 Americas 169658

Line Graph

In [11]:
# Create a line graph for sales for each region across years
line_chart = px.line(r_market, x="Year", y="Sales", color="Region", markers=True, title="Sales For Each Region Across Years", height=600, template="plotly_dark")

line_chart.update_traces(mode="markers+lines", hovertemplate=None)
line_chart.update_layout(hovermode="x unified", title_x=0.5)
line_chart.show()


#fig.update_layout(hovermode="closest")
#fig.layout.update(showlegend=False) 
#fig.update_layout(title_text='Your title', title_x=0.5)

Bar Graph

In [13]:
# Create a bar chart with custom color and animation
bar_chart = px.bar(
    r_market, 
    x="Region", 
    y="Sales", 
    animation_frame="Year", 
    animation_group="Region", 
    hover_name="Region", 
    range_y=[0, 180000], 
    height=600, 
    template="plotly_dark",
    text_auto='.2s',
    title="Sales By Region"
)

bar_chart.update_yaxes(
    autorange=False,
    tick0=0,
    dtick=50000,
    visible=False
)

# Set custom color for the region
custom_color = '#4ba65f'

# Loop through frames and update traces with custom color
for i, frame in enumerate(bar_chart.frames):
    frame_data = frame.data[0]
    frame_data.update(marker_color=custom_color, marker_line_color='white',textposition="outside")

bar_chart.update_traces(marker_color = '#4ba65f', marker_line_color = 'white', textposition="outside")
bar_chart.update_layout(hovermode=False,title_x=0.5, xaxis_title="")

bar_chart.show()
In [18]:
import dash
from dash import dcc
from dash import html
from dash.dependencies import Input, Output

# Dash app
app = dash.Dash(__name__)

# Layout of the dashboard
app.layout = html.Div([
    # Second chart (Line chart)
    #dcc.Graph(id='line-chart', figure=line_chart),
    
    # First chart (Bar chart)
    dcc.Graph(id='bar-chart', figure=bar_chart)


])

if __name__ == '__main__':
    app.run_server(port=8051, debug=True)
In [ ]: